
drop table jms_dws.dws_tab_loopbag_buildbag_summary --循环袋建包汇总
create table jms_dws.dws_tab_loopbag_buildbag_summary
(
    network_type       bigint comment '网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    proxy_area_code    varchar(50) comment '建包对应网点代理区编码',
    region_code        varchar(50) comment '建包对应网点大区编码',
    transfer_type_desc bigint comment '建包类型:进港：包号b7-b9为进港包 出港：除b7-b9外的包号为出港包1代表进港,0代表出港',
    package_source     bigint comment '建包来源 1代表jt包 0代表bs包 2代表其他',
    site_code          varchar(50) comment '建包网点',
    site_name          varchar(100) comment '建包对应网点名称',
    region_name        varchar(100) comment '建包对应网点大区名称',
    proxy_area_name    varchar(100) comment '建包对应网点代理区名称',
    exact_sum          bigint comment '包内件',
    bagging_qty        bigint comment '包数量',
    getout_qty         bigint comment '流向违规数量',
    recyle_qty         bigint comment '循环袋建包数',
    exact_qty          bigint comment '非循环袋建包数',
    recyle_sum         bigint comment '循环袋包内件',
    scan_time          date comment '日期'
) engine = olap duplicate key(network_type, proxy_area_code, region_code,transfer_type_desc,package_source)
comment "循环袋建包汇总"
partition by range(scan_time)
(start ('2022-06-01') end ('2022-07-10') every (interval 1 day)
)
distributed by hash(`site_code`) buckets 4
properties (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "day",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "v2"
);
